package codelib.spring3jdbc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Component;

import codelib.spring3jdbc.dao.BaseDAO;
import codelib.spring3jdbc.dao.UserDAO;
import codelib.spring3jdbc.dao.model.User;

@Component("UserDAOImpl")
public class UserDAOImpl extends BaseDAO implements UserDAO {

	@Override
	public void create(User user) {
		String sql = "insert into t_user(user_name, password, credits, last_visit, last_ip) "
				+ "values(:userName, :password, :credits, :lastVisit, :lastIp)";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("userName", user.getUserName());
		paramMap.put("password", user.getPassword());
		paramMap.put("credits", user.getCredits());
		paramMap.put("lastVisit", user.getLastVisit());
		paramMap.put("lastIp", user.getLastIp());
		namedParameterJdbcTemplate.update(sql, paramMap);
	}

	@Override
	public List<User> retrieveAll() {
		String sql = "select * from t_user";
		return retrieve(sql);
	}

	@Override
	public List<User> retrieveByName(String userName) {
		String sql = "select * from t_user u where u.user_name like '%"
				+ userName + "%'";
		return retrieve(sql);
	}

	private List<User> retrieve(String sql) {
		List<User> listUser = jdbcTemplate.query(sql,
				new ResultSetExtractor<List<User>>() {
					@Override
					public List<User> extractData(ResultSet rs)
							throws SQLException, DataAccessException {
						List<User> listUser = new ArrayList<User>();
						while (rs.next()) {
							User user = new User();
							user.setUserId(rs.getInt("user_id"));
							user.setUserName(rs.getString("user_name"));
							user.setPassword(rs.getString("password"));
							user.setCredits(rs.getInt("credits"));
							user.setLastIp(rs.getString("last_ip"));
							user.setLastVisit(rs.getDate("last_visit"));
							listUser.add(user);
						}
						return listUser;
					}
				});
		return listUser;
	}

	@Override
	public void update(User user) {
		String sql = "update t_user t set  t.user_name=:userName, "
				+ "t.password=:password, t.credits=:credits, "
				+ "t.last_visit=:lastVisit, t.last_ip=:lastIp "
				+ "where t.user_id=:userId";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("userName", user.getUserName());
		paramMap.put("password", user.getPassword());
		paramMap.put("credits", user.getCredits());
		paramMap.put("lastVisit", user.getLastVisit());
		paramMap.put("lastIp", user.getLastIp());
		paramMap.put("userId", user.getUserId());
		namedParameterJdbcTemplate.update(sql, paramMap);
	}

	@Override
	public void delete(int userId) {
		String sql = "delete from t_user where user_id=" + userId;
		jdbcTemplate.execute(sql);
	}
}
